CREATE PROCEDURE [dbo].[asi_RenameTable] @oldTableName nvarchar(512), @newTableName nvarchar(512) AS
BEGIN
    DECLARE @oldName nvarchar(512)
    DECLARE @newName nvarchar(512)
    DECLARE @type nvarchar(6)

    -- cursor gets all of the children of table and renames them too
    DECLARE GetChildren CURSOR FOR
    SELECT [name], 'OBJECT'
      FROM sysobjects
     WHERE parent_obj = OBJECT_ID(@oldTableName)
       AND xtype = 'D' AND [name] IS NOT NULL
    UNION
    SELECT [name], 'INDEX'
      FROM sysindexes
     WHERE id = OBJECT_ID(@oldTableName) AND keycnt != 0
           AND [name] IS NOT NULL AND ([name] NOT LIKE '_WA_Sys%' AND name NOT LIKE '_dta_stat%')

    OPEN GetChildren

    FETCH GetChildren INTO @oldName, @type

    -- rename all of the table's child objects (keys, indexes, constraints)
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @newName = REPLACE(@oldName, @oldTableName, @newTableName)
        IF @newName = @oldName OR @newName IS NULL
            SET @newName = @oldName + '_OLD'

        -- renaming index has to have old table name and [ ] around column to avoid ambiguity
        IF @type = 'INDEX'
            SET @oldName = @oldTableName + '.' + '[' + @oldName + ']'

        PRINT 'sp_rename ''' + @oldName + ''', ''' + @newName + ''',''' + @type + ''''
        EXEC ('sp_rename ''' + @oldName + ''', ''' + @newName + ''',''' + @type + '''')

        FETCH GetChildren INTO @oldName, @type
    END

    CLOSE GetChildren
    DEALLOCATE GetChildren

    -- finally, rename the able itself
    EXEC ('sp_rename ''' + @oldTableName + ''', ''' + @newTableName + ''',''OBJECT''')
END


GO